﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using KaraokeManagementDTO;
using System.Data;
using System.Data.OleDb;

namespace KaraokeManagementDAO
{
    public class HangHoaDAO:DataProvider
    {
        public List<HangHoaDTO> layDanhSachHangHoa()
        {
            List<HangHoaDTO> listHangHoa = new List<HangHoaDTO>();

            List<OleDbParameter> para = new List<OleDbParameter>();

            //Câu truy vấn lấy dữ liệu
            String query = "select * from HangHoa";


            //DataTable
            DataTable dt = new DataTable();
            dt = ExecuteQuery(query, para);

            if (dt.Rows.Count == 0)
                return null;

            foreach (DataRow row in dt.Rows)
            {
                HangHoaDTO dto = new HangHoaDTO();
                dto.ID = int.Parse(row["ID"].ToString());
                dto.TenHangHoa = row["TenHangHoa"].ToString();
                dto.SoLuongTon = int.Parse(row["SoLuongTon"].ToString());
                dto.DonGia = double.Parse(row["DonGia"].ToString());
                dto.MoTaHangHoa = row["MoTaHangHoa"].ToString();
                dto.GiamGia = double.Parse(row["GiamGia"].ToString());
                dto.NhomHangHoa_ID = int.Parse(row["NhomHangHoa_ID"].ToString());
                dto.DonViTinh_ID = int.Parse(row["DonViTinh_ID"].ToString());
                listHangHoa.Add(dto);

            }


            return listHangHoa;
        }


        public HangHoaDTO layThongTinHangHoa(int MaHangHoa)
        {
            HangHoaDTO hh = new HangHoaDTO();

            List<OleDbParameter> para = new List<OleDbParameter>();

            //Câu truy vấn lấy dữ liệu
            String query = "select * from HangHoa where ID=@MaHangHoa";
            para.Add(new OleDbParameter("@MaHangHoa", MaHangHoa));

            //DataTable
            DataTable dt = new DataTable();
            dt = ExecuteQuery(query, para);

            if (dt.Rows.Count == 0)
                return null;

            DataRow row = dt.Rows[0];

            hh.ID = int.Parse(row["ID"].ToString());
            hh.TenHangHoa = row["TenHangHoa"].ToString();
            hh.SoLuongTon = int.Parse(row["SoLuongTon"].ToString());
            hh.DonViTinh_ID = int.Parse(row["DonViTinh_ID"].ToString());
            hh.DonGia = double.Parse(row["DonGia"].ToString());
            hh.MoTaHangHoa = row["MoTaHangHoa"].ToString();
            hh.GiamGia = int.Parse(row["GiamGia"].ToString());
            hh.NhomHangHoa_ID = int.Parse(row["NhomHangHoa_ID"].ToString());



            return hh;
        }

        public Boolean themHangHoa(HangHoaDTO dto)
        {
            List<OleDbParameter> para = new List<OleDbParameter>();

            //Câu truy vấn lấy dữ liệu
            String query = "insert into HangHoa(TenHangHoa, SoLuongTon, DonViTinh_ID, DonGia, MoTaHangHoa, GiamGia, NhomHangHoa_ID) ";
            query = query + "values(@TenHangHoa, @SoLuongTon, @DonViTinh_ID, @DonGia, @MoTaHangHoa, @GiamGia, @NhomHangHoa_ID)";
           para.Add(new OleDbParameter("@TenHangHoa", dto.TenHangHoa));
            para.Add(new OleDbParameter("@SoLuongTon", dto.SoLuongTon));
            para.Add(new OleDbParameter("@DonViTinh_ID", dto.DonViTinh_ID));
            para.Add(new OleDbParameter("@DonGia", dto.DonGia));
            para.Add(new OleDbParameter("@MoTaHangHoa", dto.MoTaHangHoa));
            para.Add(new OleDbParameter("@GiamGia", dto.GiamGia));
            para.Add(new OleDbParameter("@NhomHangHoa_ID", dto.NhomHangHoa_ID));


            if (ExecuteNoneQuery(query, para) < 0)
            {
                return false;
            }

            return true;
        }


        public Boolean xoaHangHoa(int MaHangHoa)
        {
            List<OleDbParameter> para = new List<OleDbParameter>();

            //Câu truy vấn lấy dữ liệu
            String query = "delete from HangHoa where ID=@MaHangHoa";
            para.Add(new OleDbParameter("@ID", MaHangHoa));

            if (ExecuteNoneQuery(query, para) < 0)
            {
                return false;
            }

            return true;
        }


        public Boolean capNhatHangHoa(HangHoaDTO dto)
        {
            List<OleDbParameter> para = new List<OleDbParameter>();

            //Câu truy vấn lấy dữ liệu
            String query = "update HangHoa set TenHangHoa=@TenHangHoa, SoLuongTon=@SoLuongTon, DonViTinh_ID=@DonViTinh_ID, DonGia=@DonGia, MoTaHangHoa=@MoTaHangHoa, GiamGia=@GiamGia, NhomHangHoa_ID=@NhomHangHoa_ID where ID=@ID";
            para.Add(new OleDbParameter("@ID", dto.ID));
            para.Add(new OleDbParameter("@TenHangHoa", dto.TenHangHoa));
            para.Add(new OleDbParameter("@SoLuongTon", dto.SoLuongTon));
            para.Add(new OleDbParameter("@DonViTinh_ID", dto.DonViTinh_ID));
            para.Add(new OleDbParameter("@DonGia", dto.DonGia));
            para.Add(new OleDbParameter("@MoTaHangHoa", dto.MoTaHangHoa));
            para.Add(new OleDbParameter("@GiamGia", dto.GiamGia));
            para.Add(new OleDbParameter("@NhomHangHoa_ID", dto.NhomHangHoa_ID));

            if (ExecuteNoneQuery(query, para) < 0)
            {
                return false;
            }

            return true;
        }


    }
}
